*============================================================================================================================*
* Task: examine people's aviodance behavior when facing water pollution using CHNS data and surface water pollution data
*============================================================================================================================*

#delimit ; 

set more off ;     pause on ;      

*** set path here

tempfile pollution pipedwater income f1 f2 ; 

*====================================================================================================================================================;
* Table 9. Consumption of Bottled Water in Regions with Different Surface Water Pollution ; 
*====================================================================================================================================================;
use $path\Water_Pollution_1999_2010_clean1_26_2017.dta, clear ; 
keep if year>=2006 ;            
expand 2 if year==2010, gen(x) ; 
replace year=2011 if x==1 ;  
expand 2 if year==2010, gen(y) ; 
replace year=2015 if y==1 ;  

ren year wave ; 
ren 水质 waterquality ; 
ren 省 province ;  
gen code=. ; 
replace code=11	if province=="北京" ;  
replace code=21	if province=="辽宁" ;  
replace code=23	if province=="黑龙江" ;  
replace code=31	if province=="上海" ;  
replace code=32	if province=="江苏" ;  
replace code=37	if province=="山东" ;  
replace code=41	if province=="河南" ;  
replace code=42	if province=="湖北" ;  
replace code=43	if province=="湖南" ;  
replace code=45	if province=="广西" ;  
replace code=55	if province=="重庆" ;    
replace code=52	if province=="贵州" ;  

collapse waterquality , by(code wave) ;  
save `pollution' ;  


use "path\hhinc_10.dta", clear ;   
keep hhinc_pc hhincpc_cpi hhinc_cpi hhinc hhincgross wave hhid ; 
save `income' ;                                                              
                                                                               

use "path\asset_12.dta", clear ;  
bysort wave: sum L1 L2 L3 L1C ; 
keep wave hhid L1 L2 L3 L1A L1B L1C L2A L3A L4 T* ; 
keep if wave>=2006  ; 
tab L1 ; 
tab L3 ; 
tab L3 L1 ;  
bysort wave hhid: gen x=_N ; 
drop if L1==. & x>1 ; 
recode L3 (2/4=1) (5=0) (1 6 7 9=.), gen(surfacewater) ;  
gen rural=T2==2 ;  
rename T1 code ; 
drop x ; 
save `pipedwater' ; 



use "path\pexam_00.dta" ;    // 2015 missing  
bysort wave: sum U424 ;   // spring water, purified water, distilled water 
keep if wave>=2006  ;   


merge m:1 wave hhid using `pipedwater' ; 
drop _merge ; 
merge m:1 wave hhid using `income' ; 
drop _merge ; 
merge m:1 wave code using `pollution' ; 
drop _merge ;  
drop if code==. ;  

keep if wave==2006 | wave==2009 |wave==2011 | wave==2015 ;  
recode U424 (9=.), gen(bottlewater) ;   

bysort code: sum surfacewater ;  
bysort code: sum bottlewater ;        
bysort code: sum waterquality ;        

*drop if code==11 | code==31 | code==50 | code==. ;    // only one obs for those code values 

expand 2, gen(x) ; 
replace rural=2 if x==1 ;   
label define x 0 "Urban" 1 "Rural" 2 "Rural&Urban" ; 
label values rural x ; 

* gen lowwaterquality=waterquality>3 ; 
egen wqcat = cut(waterquality), at(0,2,4,6) icodes ;    // 0 is type I&II, 1 is type III&IV, 2 is type V&VI  
gen respondents=1 ;  
save `f1' ; 

drop if wqcat==1 ; 
reg bottlewater waterquality wave hhincpc_cpi rural ; 
reg bottlewater waterquality wave hhincpc_cpi if rural==1 ; 
reg bottlewater waterquality if rural==1 ; 
xi: reg bottlewater i.wqcat wave hhincpc_cpi rural ; 
xi: reg bottlewater i.wqcat wave hhincpc_cpi if rural==1 ; 
xi: reg bottlewater i.wqcat hhincpc_cpi if rural==1 ; 

reg surfacewater waterquality wave hhincpc_cpi rural ; 
reg surfacewater waterquality wave hhincpc_cpi if rural==1 ; 
reg surfacewater waterquality if rural==1 ; 
xi: reg surfacewater i.wqcat wave hhincpc_cpi rural ; 
xi: reg surfacewater i.wqcat wave hhincpc_cpi if rural==1 ; 
xi: reg surfacewater i.wqcat if rural==1 ; 

*** respondents do not use surface water  ; 
use `f1', clear ;  
keep if surfacewater==0 ;   
preserve ;
drop if wqcat==1 ;   // do not use areas with type III&IV water quality  
bysort wave rural: ttest bottlewater, by(wqcat) ; 
restore ; 
collapse bottlewater surfacewater waterquality (sum) respondents, by(wave rural wqcat) ; 
sort wave rural wqcat ; 
edit ; 
gen sample="Non-surfacewater user" ; 
save `f2' ; 

*** respondents using surface water ; 
use `f1', clear ; 
keep if surfacewater==1 ;   
preserve ;
drop if wqcat==1 ;    // do not use areas with type III&IV water quality  
drop if wave==2009 & rural==0 ;  
bysort wave rural: ttest bottlewater, by(wqcat) ; 
restore ; 

collapse bottlewater surfacewater waterquality (sum) respondents, by(wave rural wqcat) ;  
sort wave rural wqcat ; 
edit ; 
gen sample="Surfacewater user" ;  
append using `f2' ; 


format surfacewater bottlewater waterquality %4.3f ;  
export excel using chns.xlsx, firstrow(var) keepcellfmt replace ; 
 

